package cn.dida.dao;

import cn.dida.model.LgsDepot;
import cn.dida.model.Pageable;
import org.apache.commons.lang3.ArrayUtils;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Created by zhangzhiling on 2017/2/8.
 */
@Repository
public class LgsDepotDao extends DaoBase {

    public Pageable<LgsDepot> query(Integer companyId, String keyword, Integer sta,  int page) {

        Object[] args = {};
        StringBuffer sqlQueryResultCount = new StringBuffer("select count(1) from lgs_depot ");
        StringBuffer sqlQueryResult = new StringBuffer("select ID, company_id, name, address, sta, c_time, c_admin from lgs_depot ");

        StringBuffer sqlWhere = new StringBuffer("where company_id = ? ") ;
        args = ArrayUtils.add(args, companyId);
        if(keyword != null && keyword != ""){
            sqlWhere.append("and name like ? ");
            args = ArrayUtils.add(args, "%" + keyword + "%");
        }
        if(sta != null){
            sqlWhere.append("and sta = ? ");
            args = ArrayUtils.add(args, sta);
        }


        // 查询记录总数
        Integer resultTotal = jdbcTemplate.queryForObject(sqlQueryResultCount.append(sqlWhere).toString(), Integer.class, args);
        // 查询记录
        sqlQueryResult.append(sqlWhere).append("  order by c_time desc limit ?,?");
        args = ArrayUtils.add(args, (page - 1) * getPageSize());
        args = ArrayUtils.add(args, getPageSize());
        List<LgsDepot> result = jdbcTemplate.query(sqlQueryResult.toString(), args, new BeanPropertyRowMapper<>(LgsDepot.class));
        return new Pageable<>(page, getPageSize(), resultTotal, result);

    }

    public Integer insert(LgsDepot lgsDepot){

        String sql = "insert into lgs_depot(company_id, name, address, sta, c_time, c_admin) " +
                "values (:companyId, :name, :address, :sta, :cTime, :cAdmin)";

        lgsDepot.setcTime(new Date().getTime());

        KeyHolder keyHolder=new GeneratedKeyHolder();
        SqlParameterSource ps=new BeanPropertySqlParameterSource(lgsDepot);
        namedParameterJdbcTemplate.update(sql, ps, keyHolder);
        return keyHolder.getKey().intValue();
    }

    public Integer update(LgsDepot lgsDepot){

        String sql = "update lgs_depot set name=:name, address=:address, sta=:sta where ID =" + lgsDepot.getId();

        HashMap<String, Object> paramMap = new HashMap<>();
        paramMap.put("name", lgsDepot.getName());
        paramMap.put("address", lgsDepot.getAddress());
        paramMap.put("sta", lgsDepot.getSta());

        return namedParameterJdbcTemplate.update(sql, paramMap);
    }


    public LgsDepot getLgsDepot(int id) {
        String sql = "select ID, company_id, name, address, sta, c_time, c_admin from lgs_depot where id = ?";
        List<LgsDepot> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<LgsDepot>(LgsDepot.class), id);
        if (null != list && list.size() == 1) {
            return list.get(0);
        }
        return null;
    }

    /**
     * 获取状态正常的地点列表（仓库）创建、修改pda用户时使用
     * @param companyId
     * @param sta
     * @return
     */
    public List<LgsDepot> getList(Integer companyId, Integer sta){

        String sql = "select ID 'id', name, company_id 'companyId', address from lgs_depot where company_id = ? and sta = ? order by c_time desc";

        List<LgsDepot> result = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(LgsDepot.class), companyId, sta);
        return result;
    }


    /**
     * 验证地点名称是否已存在
     * @param companyId
     * @param name
     * @return
     */
    public LgsDepot testName( Integer companyId, String name){

        String sql = "select ID, company_id, name, address, sta, sta, c_time, c_admin from lgs_depot where company_id=? and name = ?" ;
        List<LgsDepot>  list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<LgsDepot>(LgsDepot.class), companyId, name);
        if (null != list && list.size() == 1) {
            return list.get(0);
        }
        return null;
    }
}
